////////////////////////////////////////////////////////////////////////////////
// Copyright 2005 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   MENU_GROUP_LABEL
// AUTHOR           :   Victor Zheng 
// DATE CREATE      :   2005/12/21
// DESCRIPTION      :   a DAO file for the function, MENU_GROUP_LABEL
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2005/12/21   Victor Zheng    1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////
 
package com.erp.app.admin.menuGroupLabel.dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.erp.app.admin.menuGroupLabel.criteria.MenuGroupLabelSrchCriteria;
import com.erp.app.admin.menuGroupLabel.pk.MenuGroupLabelPk;
import com.erp.app.admin.menuGroupLabel.vo.MenuGroupLabelVo;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
 
/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Victor Zheng 
* @version 1.0     2005/12/21
* @since           FrameWork 1.0
*
*/
public class MenuGroupLabelDao extends SimplePageEditStyleDao {
 
    /**
    * Returns the SQL select statement.
    * @param         obj     the searching criteria
    * @return        a string containing sql query
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlQuery(SimpleBaseSrchCriteria obj) {
 
        MenuGroupLabelSrchCriteria criteria = (MenuGroupLabelSrchCriteria) obj;
        String whereClause = toWhereClause(criteria);
        String groupByClause = "";
        String orderByClause = "ORDER BY COMPANY_CODE, GROUP_ID, MENU_ID, LANG_CODE";
 
        String sql = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, LANG_CODE, MENU_NAME, "
            + "MENU_WEB_LABEL, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, "
            + "MENU_TYPE "
            + "FROM T_MENU_GROUP_LABEL "
            + whereClause
            + groupByClause
            + orderByClause;
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Returns the where clause of a SQL select statement.
    * @param         obj     the searching criteria
    * @return        a string containing the where clause
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String toWhereClause(MenuGroupLabelSrchCriteria crit) {
 
        MenuGroupLabelSrchCriteria criteria = (MenuGroupLabelSrchCriteria) crit;
        StringBuffer whereClause = new StringBuffer();
        
        whereClause.append("WHERE COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
        
        if (criteria.getSrchGroupIdFr() != null) {
            whereClause.append(" AND GROUP_ID >= '"
                + criteria.getSrchGroupIdFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchGroupIdTo() != null) {
            whereClause.append(" AND GROUP_ID <= '"
                + criteria.getSrchGroupIdTo().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchMenuIdFr() != null) {
            whereClause.append(" AND MENU_ID >= '"
                + criteria.getSrchMenuIdFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchMenuIdTo() != null) {
            whereClause.append(" AND MENU_ID <= '"
                + criteria.getSrchMenuIdTo().toUpperCase().trim() + "' ");
        }
                
        if (criteria.getSrchLangCodeFr() != null) {
            whereClause.append(" AND LANG_CODE >= '"
                + criteria.getSrchLangCodeFr().trim() + "' ");
        }
        if (criteria.getSrchLangCodeTo() != null) {
            whereClause.append(" AND LANG_CODE <= '"
                + criteria.getSrchLangCodeTo().trim() + "' ");
        }
        if (criteria.getSrchMenuNameFr() != null) {
            whereClause.append(" AND MENU_NAME >= '"
                + criteria.getSrchMenuNameFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchMenuNameTo() != null) {
            whereClause.append(" AND MENU_NAME <= '"
                + criteria.getSrchMenuNameTo().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchMenuWebLabelFr() != null) {
            whereClause.append(" AND MENU_WEB_LABEL >= '"
                + criteria.getSrchMenuWebLabelFr().toUpperCase().trim() + "' ");
        }
        if (criteria.getSrchMenuWebLabelTo() != null) {
            whereClause.append(" AND MENU_WEB_LABEL <= '"
                + criteria.getSrchMenuWebLabelTo().toUpperCase().trim() + "' ");
        }
 
        return whereClause.toString();
    }
 
    /**
    * Returns an array list of child VOs by the parent
    * @param         parent   the incoming parent VO
    * @param         conn     the database connection
    * @return        an array list of child VOs
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
        throws FindRecordException {
 
        return null;
    }
 
    /**
    * Constructs a string of SQL insert statement
    * @return        a string of SQL insert statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlInsert() {
 
        String sql = "INSERT INTO T_MENU_GROUP_LABEL ("
            + "COMPANY_CODE, GROUP_ID, MENU_ID, LANG_CODE, MENU_NAME, "
            + "MENU_WEB_LABEL, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, "
            + "MENU_TYPE) "
            + "VALUES ("
            + "?, ?, ?, ?, ?, "
            + "?, ?, SYSDATE, ?, ?, "
            + "?)";
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL insert or update statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
        throws SQLException {
 
        MenuGroupLabelVo vo = (MenuGroupLabelVo) obj;
        int i = 1;
 
        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getGroupId());
        pStmt.setString(i++, vo.getMenuId());
        pStmt.setString(i++, vo.getLangCode());
        setNullableString(pStmt, i++, vo.getMenuName());
        setNullableString(pStmt, i++, vo.getMenuWebLabel());
        setNullableString(pStmt, i++, vo.getCreateUser());
 
        if (ProjectConstants.DB_OP_UPDATE.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getCreateDate());
 
        setNullableString(pStmt, i++, vo.getUpdateUser());
 
        if (ProjectConstants.DB_OP_INSERT.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        setNullableString(pStmt, i++, vo.getMenuType());
 
        if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setString(i++, vo.getGroupId());
            pStmt.setString(i++, vo.getMenuId());
            pStmt.setString(i++, vo.getLangCode());
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        }
    }
 
    /**
    * Sets the values for a SQL insert statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
    }
 
    /**
    * Constructs a string of SQL update statement
    * @return        a string of SQL update statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlUpdate() {
 
        String sql = "UPDATE T_MENU_GROUP_LABEL SET COMPANY_CODE=?, GROUP_ID=?, MENU_ID=?, LANG_CODE=?, "
            + "MENU_NAME=?, "
            + "MENU_WEB_LABEL=?, CREATE_USER=?, CREATE_DATE=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE, "
            + "MENU_TYPE=? "
            + "WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND LANG_CODE=? "
            + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL update statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForModify(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
    }
 
    /**
    * Constructs a string of SQL delete statement
    * @return        a string of SQL update statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlDelete() {
        String sql = "DELETE FROM T_MENU_GROUP_LABEL WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND LANG_CODE=?";        
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL delete statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        MenuGroupLabelVo vo = (MenuGroupLabelVo) obj;
        int i = 1;
 
        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getGroupId());
        pStmt.setString(i++, vo.getMenuId());
        pStmt.setString(i++, vo.getLangCode());
    }
 
    /**
    * Constructs a string of SQL select statement
    * @return        a string of SQL select statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlFind() {
 
        String sql = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, LANG_CODE, MENU_NAME, "
            + "MENU_WEB_LABEL, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, "
            + "MENU_TYPE "
            + "FROM T_MENU_GROUP_LABEL "
            + "WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND LANG_CODE=? "
            + "ORDER BY COMPANY_CODE, GROUP_ID, MENU_ID, LANG_CODE";
 
        //super.logSqlQuery(sql);
        return sql;
    }
 
    /**
    * Sets the values for a SQL select statement for finding by primary key.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming PK
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForFind(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
        MenuGroupLabelPk pk = (MenuGroupLabelPk) obj;
        int i = 1;
 
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getGroupId());
        pStmt.setString(i++, pk.getMenuId());
        pStmt.setString(i++, pk.getLangCode());
    }
 
    /**
    * Constructs a string of SQL select statement
    * @return        a string of SQL select statement
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlList() {
 
        return null;
    }
 
    /**
    * Sets the values for a SQL select statement for finding child tables.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming parent PK
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForList(PreparedStatement pStmt, Object obj)
        throws SQLException {
 
    }
 
    /**
    * Sets the values of a result set to a vo
    * @param         rs   the incoming result set
    * @return        a vo containing the result set values
    *
    * @author     Victor Zheng 
    * @since      FrameWork 1.0
    */
    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
 
        MenuGroupLabelVo vo = new MenuGroupLabelVo();
 
        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setGroupId(rs.getString("GROUP_ID"));
        vo.setMenuId(rs.getString("MENU_ID"));
        vo.setLangCode(rs.getString("LANG_CODE"));
        vo.setMenuName(rs.getString("MENU_NAME"));
        vo.setMenuWebLabel(rs.getString("MENU_WEB_LABEL"));
        vo.setCreateUser(rs.getString("CREATE_USER"));
        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        vo.setUpdateUser(rs.getString("UPDATE_USER"));
        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        vo.setMenuType(rs.getString("MENU_TYPE"));
 
        return vo;
    }
 
}
